package com.erp.app.admin.companyMenu.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.admin.companyMenu.beans.CompanyMenuGroupSearchVO;
import com.erp.app.admin.companyMenu.beans.CompanyMenuGroupVO;
import com.erp.app.admin.companyMenu.ejb.CompanyMenuGroupPK;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;

public class CompanyMenuGroupDAO extends SimplePageEditStyleDao {

	protected String sqlPurge(){
        return sqlDelete();
    }

    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForDelete(pStmt, obj);
    }

    protected int getTablesInEffect() {
        return 1;
    }

    private String toWhereClause(CompanyMenuGroupSrchCriteria criteria) {
        StringBuffer whereClause = new StringBuffer();

        if (criteria.getSrchCompanyCode() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchMenuId() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.MENU_ID = '" + criteria.getSrchMenuId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchGroupId() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.GROUP_ID = '" + criteria.getSrchGroupId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchMenuName() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("UPPER(X.MENU_NAME) LIKE '%" + criteria.getSrchMenuName().trim().toUpperCase() + "%' ");
        }
        if (criteria.getSrchMenuType() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.MENU_TYPE = '" + criteria.getSrchMenuType().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchParentMenuId() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.PARENT_MENU_ID = '" + criteria.getSrchParentMenuId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchRoleGroupId() != null) {
            whereClause.append(whereClause.length() == 0 ? " " : "AND ");
            whereClause.append("X.ROLE_GROUP_ID = '" + criteria.getSrchRoleGroupId().trim().toUpperCase() + "' ");
        }

        if (whereClause.length() > 0)
            return " AND " + whereClause.toString();
        else
            return "";
    }

    public Collection findByCriteria(Object obj, Connection conn) {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();

        try {
            CompanyMenuGroupSrchCriteria criteria = (CompanyMenuGroupSrchCriteria) obj;
            String query = "SELECT X.ROLE_GROUP_ID ROLE_GROUP_ID, X.GROUP_ID GROUP_ID, X.MENU_ID MENU_ID,"
                    + " X.PARENT_MENU_ID PARENT_MENU_ID, X.MENU_NAME MENU_NAME, X.MENU_TYPE MENU_TYPE,"
                    + " X.MENU_ORDER MENU_ORDER, X.MENU_URL MENU_URL,"
                    + " X.COMPANY_CODE COMPANY_CODE, X.DESCRIPTION DESCRIPTION," + " DECODE(C.COMPANY_CODE,NULL,'"+ProjectConstants.GENERIC_NO+"','"+ProjectConstants.GENERIC_YES+"') EXIST_IND"
                    + " FROM (SELECT B.ROLE_GROUP_ID ROLE_GROUP_ID, A.GROUP_ID GROUP_ID, A.MENU_ID MENU_ID,"
                    + " A.PARENT_MENU_ID PARENT_MENU_ID, A.MENU_NAME MENU_NAME, A.MENU_TYPE MENU_TYPE, "
                    + "  A.MENU_ORDER MENU_ORDER, A.MENU_URL MENU_URL, "
                    + "  B.COMPANY_CODE COMPANY_CODE, B.DESCRIPTION DESCRIPTION" + "  FROM T_MENU_GROUP A, T_ROLE_GROUP B"
                    + "  WHERE A.COMPANY_CODE  = B.COMPANY_CODE) X" + " , T_COMPANY_MENU_GROUP C"
                    + " WHERE X.COMPANY_CODE  = C.COMPANY_CODE  (+)" + " AND X.ROLE_GROUP_ID = C.ROLE_GROUP_ID (+)"
                    + " AND  X.MENU_ID    = C.MENU_ID (+)" + toWhereClause(criteria)
                    + " ORDER BY X.ROLE_GROUP_ID, X.GROUP_ID, X.PARENT_MENU_ID," + " X.MENU_ORDER, X.MENU_ID, X.MENU_TYPE";

            Logger.sql(this, "findByCriteria(), query=" + query);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    rslt.add(getResultSet(rs));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
            	Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
            	Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }

    protected String sqlInsert() {
        return "INSERT INTO T_COMPANY_MENU_GROUP ("
                + "COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, VIEW_LEVEL, CREATE_LEVEL, UPDATE_LEVEL"
                + ", DELETE_LEVEL, CREATE_USER, CREATE_DATE) " + "VALUES (?,?,?,?,?,?,?,?,?,sysdate)";
    }

    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupVO companyMenuGroupVO = (CompanyMenuGroupVO) obj;
        int i = 1;
        pStmt.setString(i++, companyMenuGroupVO.getCompanyCode());
        pStmt.setString(i++, companyMenuGroupVO.getGroupId());
        pStmt.setString(i++, companyMenuGroupVO.getMenuId());
        pStmt.setString(i++, companyMenuGroupVO.getRoleGroupId());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getViewLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getCreateLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getUpdateLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getDeleteLevel());
        setNullableString(pStmt, i++, companyMenuGroupVO.getCreateUser());
    }

    protected String sqlUpdate() {
        return "UPDATE T_COMPANY_MENU_GROUP SET "
                + "VIEW_LEVEL=?, CREATE_LEVEL=?, UPDATE_LEVEL=?, DELETE_LEVEL=?, UPDATE_USER=?, UPDATE_DATE=sysdate "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? "
                + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
    }

    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupVO companyMenuGroupVO = (CompanyMenuGroupVO) obj;
        int i = 1;
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getViewLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getCreateLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getUpdateLevel());
        setNullableBigDecimal(pStmt, i++, companyMenuGroupVO.getDeleteLevel());
        setNullableString(pStmt, i++, companyMenuGroupVO.getUpdateUser());
        pStmt.setString(i++, companyMenuGroupVO.getCompanyCode());
        pStmt.setString(i++, companyMenuGroupVO.getGroupId());
        pStmt.setString(i++, companyMenuGroupVO.getMenuId());
        pStmt.setString(i++, companyMenuGroupVO.getRoleGroupId());
        setNullableTimestamp(pStmt, i++, companyMenuGroupVO.getUpdateDate());
    }

    protected String sqlDelete() {
        return "DELETE FROM T_COMPANY_MENU_GROUP" + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? "
                + " AND ROLE_GROUP_ID = ? ";
    }

    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupVO companyMenuGroupVO = (CompanyMenuGroupVO) obj;
        int i = 1;
        pStmt.setString(i++, companyMenuGroupVO.getCompanyCode());
        pStmt.setString(i++, companyMenuGroupVO.getGroupId());
        pStmt.setString(i++, companyMenuGroupVO.getMenuId());
        pStmt.setString(i++, companyMenuGroupVO.getRoleGroupId());
    }

    protected String sqlFind() {
        return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, VIEW_LEVEL, CREATE_LEVEL, UPDATE_LEVEL"
                + ", DELETE_LEVEL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_COMPANY_MENU_GROUP "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? ";

    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupPK pk = (CompanyMenuGroupPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getRoleGroupId());
    }

    protected String sqlList() {
        return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, ROLE_GROUP_ID, VIEW_LEVEL, CREATE_LEVEL, UPDATE_LEVEL"
                + ", DELETE_LEVEL, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + "FROM T_COMPANY_MENU_GROUP "
                + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? " + "AND ROLE_GROUP_ID = ? ";
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {
        CompanyMenuGroupPK pk = (CompanyMenuGroupPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
        pStmt.setString(i++, pk.getRoleGroupId());
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
        CompanyMenuGroupVO vo = new CompanyMenuGroupVO();
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setGroupId(rs.getString("GROUP_ID"));
        vo.setMenuId(rs.getString("MENU_ID"));
        vo.setViewLevel(rs.getBigDecimal("VIEW_LEVEL"));
        vo.setCreateLevel(rs.getBigDecimal("CREATE_LEVEL"));
        vo.setUpdateLevel(rs.getBigDecimal("UPDATE_LEVEL"));
        vo.setDeleteLevel(rs.getBigDecimal("DELETE_LEVEL"));
        vo.setRoleGroupId(rs.getString("ROLE_GROUP_ID"));
        vo.setCreateUser(rs.getString("CREATE_USER"));
        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        vo.setUpdateUser(rs.getString("UPDATE_USER"));
        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        return vo;
    }

    protected SimpleBaseVo getResultSet(ResultSet rs) throws SQLException {
        CompanyMenuGroupSearchVO vo = new CompanyMenuGroupSearchVO();
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));

        vo.setRoleGroupId(rs.getString("ROLE_GROUP_ID"));
        vo.setGroupId(rs.getString("GROUP_ID"));
        vo.setMenuId(rs.getString("MENU_ID"));
        vo.setParentMenuId(rs.getString("PARENT_MENU_ID"));
        vo.setMenuName(rs.getString("MENU_NAME"));
        vo.setMenuType(rs.getString("MENU_TYPE"));
        vo.setMenuOrder(rs.getString("MENU_ORDER"));
        vo.setMenuUrl(rs.getString("MENU_URL"));
        vo.setCompanyCode(rs.getString("COMPANY_CODE"));
        vo.setDescription(rs.getString("DESCRIPTION"));
        vo.setExistInd(rs.getString("EXIST_IND"));

        return vo;
    }

    public final ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
        // dummy method
        return null;
    }

    public final ChildList selectByParent(SimpleBaseVo parent, Class childType, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ChildList rslt = new ChildList(childType);

        try {
            for (short i = 0, j; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlList(i));
                setValuesForList(pStmt, parent.getPrimaryKey(), i);

                j = 0;
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    if (i == 0) {
                        SimpleBaseVo childVO = (SimpleBaseVo) getResultSetValues(rs, null, i);
                        childVO.setParent(parent);
                        childVO.setIsExisting(true);
                        childVO.setIsDirty(false);

                        rslt.add(childVO);
                    } else {
                        SimpleBaseVo childVO = (SimpleBaseVo) rslt.get(j);
                        if (childVO != null) {
                            childVO = (SimpleBaseVo) getResultSetValues(rs, childVO, i);
                            rslt.set(j, childVO);
                        }
                        j++;
                    }
                }
            }
        } catch (SQLException sqe) {
            System.err.println("CompanyMenuGroupDAO.selectByParent(): " + (sqe.getMessage()));
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuGroupDAO.selectByParent() [" + childType.getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuGroupDAO.selectByParent() [" + childType.getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return rslt;
    }

    protected String sqlList(short i) {
        if (getTablesInEffect() == 1) {
            return sqlList();
        } else {
            throw new UnsupportedOperationException("CompanyMenuGroupDAO.sqlList was not implemented.");
        }
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            setValuesForList(pStmt, obj);
        } else {
            throw new UnsupportedOperationException("CompanyMenuGroupDAO.setValuesForList was not implemented.");
        }
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs, SimpleBaseVo model, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            return getResultSetValues(rs);
        } else {
            throw new UnsupportedOperationException("CompanyMenuGroupDAO.getResultSetValues was not implemented.");
        }
    }

    public final SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) throws FindRecordException {
        return findByPrimaryKey((SimpleBasePk) obj, conn);
    }

    public final SimpleBaseVo findByPrimaryKey(SimpleBasePk pk, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        SimpleBaseVo model = null;

        try {
            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlFind(i));
                setValuesForFind(pStmt, pk, i);

                rs = pStmt.executeQuery();

                if (rs.next()) {
                    if (i == 0) {
                        model = (SimpleBaseVo) getResultSetValues(rs, null, i);
                    } else {
                        model = (SimpleBaseVo) getResultSetValues(rs, model, i);
                    }
                }
            }
        } catch (SQLException sqe) {
        	Logger.logExceptions(this, sqe);
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuGroupDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("CompanyMenuGroupDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return model;
    }

    protected String sqlFind(short i) {
        if (getTablesInEffect() == 1) {
            return sqlFind();
        } else {
            throw new UnsupportedOperationException("CompanyMenuGroupDAO.sqlFind was not implemented.");
        }
    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj, short i) throws SQLException {
        if (getTablesInEffect() == 1) {
            setValuesForFind(pStmt, obj);
        } else {
            throw new UnsupportedOperationException("CompanyMenuGroupDAO.setValuesForFind was not implemented.");
        }
    }

    public void updateMenuObject(String compCode, String groupID, String menuID, String roleGroupID, String crtMod,
            String uptMod, String delMod, Connection conn) throws Exception {

        String stmt = "{ call sp_upd_menu_object(?, ?, ?, ?, ?, ?, ?, ?) }";
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();
        CallableStatement cStmt = null;

        try {
            cStmt = conn.prepareCall(stmt);
            int i = 1;

            cStmt.setString(i++, compCode.trim().toUpperCase());
            cStmt.setString(i++, groupID.trim().toUpperCase());
            cStmt.setString(i++, menuID.trim().toUpperCase());
            cStmt.setString(i++, roleGroupID.trim().toUpperCase());
            cStmt.setString(i++, crtMod.trim().toUpperCase());
            cStmt.setString(i++, uptMod.trim().toUpperCase());
            cStmt.setString(i++, delMod.trim().toUpperCase());
            cStmt.registerOutParameter(i++, java.sql.Types.VARCHAR); // err
                                                                        // msg

            cStmt.executeUpdate();

            String errMsg = cStmt.getString(8);

            if (errMsg != null) {
                System.err.println("error in CompanyMenuGroupDAO.updateMenuObject(): errMsg = [" + errMsg + "]");
                throw new Exception(errMsg);
            }

        } catch (Exception e) {            
            Logger.logExceptions(this, e); 
            throw new Exception(e.getMessage());
        } finally {
            try {
                if (cStmt != null) {
                    cStmt.close();
                }
            } catch (Exception e) {
                System.err.println("Error closing stmt: " + e.getMessage());
            }
        }
    }

}
